# -*- coding: utf-8 -*-
# =============================================================================
#     FileName:
#         Desc:
#       Author:
#        Email:
#     HomePage:
#      Version:
#   LastChange:
#      History:
# =============================================================================
import datetime
import json
import logging
import traceback
import uuid

import mysql.connector
import mysql.connector.pooling
from mysql.connector.conversion import MySQLConverter


class StringTimeConverter(MySQLConverter):
    """
    重写将MySQL数据类型转换成Python类型的方法，返回字符串类型，以避免错误转换时间导致异常。
    如MySQL中可以录入零值时间，如"0000-00-00 00:00:00",但转换为Python时间类型就会报错。
    """

    CONVERT_ERROR1 = "Could not convert '{value}' to python {pytype}"

    def _DATE_to_python(self, value, dsc=None):  # pylint: disable=C0103
        """Converts TIME column MySQL to a python datetime.datetime type.

        Raises ValueError if the value can not be converted.

        Returns DATE column type as datetime.date type.
        """
        return value.decode("utf-8")

    def _DATETIME_to_python(self, value, dsc=None):  # pylint: disable=C0103
        """
        Converts DATETIME column value to python datetime.time value type.

        Converts the DATETIME column MySQL type passed as bytes to a python
        datetime.datetime type.

        Returns: datetime.datetime type.
        """
        return value.decode("utf-8")

    def _TIME_to_python(self, value, dsc=None):  # pylint: disable=C0103
        """
        Returns TIME column type as datetime.time type.
        """
        return value.decode("utf-8")

    _TIMESTAMP_to_python = _DATETIME_to_python


class MySQLJsonEncoder(json.JSONEncoder):
    def format_object(self, object_item):
        if object_item is None:
            return 'None'
        elif isinstance(object_item, dict):
            new_dict = dict()
            for item_key in dict(object_item).keys():
                new_dict[item_key] = self.format_object(object_item[item_key])
            return new_dict
        elif isinstance(object_item, list):
            new_list = list()
            for list_item in dict(object_item).items():
                new_list.append(self.format_object(list_item))
            return new_list
        elif isinstance(object_item, datetime.datetime):
            return object_item.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(object_item, datetime.date):
            return object_item.strftime('%Y-%m-%d')
        else:
            return str(object_item)

    def default(self, object_item):
        return self.format_object(object_item)


class MySQLClient(object):

    def __init__(self, mysql_host: str, mysql_port: int, mysql_user: str, mysql_password: str,
                 database_name: str, connect_timeout: int = 10, mysql_charset: str = "utf8mb4",
                 mysql_collation="utf8mb4_general_ci", logger=None):
        self.mysql_host = mysql_host
        self.mysql_user = mysql_user
        self.mysql_password = mysql_password
        self.mysql_port = mysql_port
        self.connect_timeout = connect_timeout
        self.mysql_charset = mysql_charset
        self.mysql_collation = mysql_collation
        self.database_name = database_name
        self.mysql_pool_name = str(uuid.uuid4())
        self.mysql_pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name=self.mysql_pool_name, pool_size=5, host=self.mysql_host,
            port=self.mysql_port, user=self.mysql_user, password=self.mysql_password,
            database=self.database_name, connect_timeout=self.connect_timeout,
            charset=self.mysql_charset, collation=self.mysql_collation
        )
        if logger is None:
            logger = logging.getLogger("mysql")
        self.logger = logger

    def _get_connection(self):
        """
        获取当前服务器的MySQL连接
        :return:
        """
        conn = self.mysql_pool.get_connection()
        conn.ping(reconnect=True, attempts=3)
        return conn

    def check_connection(self):
        """
        检查数据库链接是否正常，
        :return: 连接正常则返回True，连接异常则返回False
        """
        try:
            self.mysql_query(sql_script="select 1;")
            return True
        except Exception as _:
            return False

    @classmethod
    def _set_max_execute_seconds(cls, cursor, max_execute_seconds):
        """
        尝试设置会话级别的最大执行超时时间
        :param cursor:
        :param max_execute_seconds:
        :return:
        """
        try:
            cursor.execute("set max_statement_time={};".format(max_execute_seconds * 1000))
        except Exception as _:
            pass
        try:
            cursor.execute("set max_execution_time={}".format(max_execute_seconds * 1000))
        except Exception as _:
            pass

    def mysql_query(self, sql_script, sql_paras=None, return_dict=False,
                    max_execute_seconds=0, mysql_converter=None):
        """
        执行查询并返回数据
        :param sql_script: 查询SQL
        :param sql_paras: 查询参数
        :param return_dict: 是否返回字典类型，默认为False
        :param max_execute_seconds: 最大执行时间，默认为0，即不超时
        :param mysql_converter: mysql转换器，用于在MySQL和Python之间的类型转换,默认为None
        :return: 返回查询集合
        """
        conn = None
        cursor = None
        try:
            self.logger.debug("请求地址: {}:{}\n请求SQL: {}\n请求参数: {}".format(
                self.mysql_host, self.mysql_port,
                sql_script, json.dumps(sql_paras, cls=MySQLJsonEncoder),
            ))
            conn = self._get_connection()
            if mysql_converter is not None:
                conn.set_converter_class(mysql_converter)
            if return_dict:
                cursor = conn.cursor(dictionary=True)
            else:
                cursor = conn.cursor()
            self._set_max_execute_seconds(cursor=cursor, max_execute_seconds=max_execute_seconds)
            if sql_paras is not None:
                cursor.execute(sql_script, sql_paras)
            else:
                cursor.execute(sql_script)
            exec_result = cursor.fetchall()
            conn.commit()
            return exec_result
        except Exception as ex:
            warning_message = "执行SQL出现异常,请求地址: {}:{}\n请求SQL: {}\n请求参数: {}\n异常信息:{}\n堆栈信息：{}".format(
                self.mysql_host, self.mysql_port,
                sql_script, json.dumps(sql_paras, cls=MySQLJsonEncoder),
                str(ex), traceback.format_exc(),
            )
            self.logger.warning(warning_message)
            raise Exception(str(ex))
        finally:
            if cursor is not None:
                cursor.close()
            if conn is not None:
                conn.close()

    def mysql_exec(self, sql_script, sql_paras=None):
        """
        执行传入的脚本，返回影响行数
        :param sql_script: 查询SQL
        :param sql_paras: 查询参数
        :return: 脚本语句执行的影响行数
        """
        cursor = None
        conn = None
        try:
            self.logger.debug("请求地址: {}:{}\n请求SQL: {}\n请求参数: {}".format(
                self.mysql_host, self.mysql_port,
                sql_script, json.dumps(sql_paras, cls=MySQLJsonEncoder),
            ))
            conn = self._get_connection()
            cursor = conn.cursor()
            if sql_paras is not None:
                cursor.execute(sql_script, sql_paras)
            else:
                cursor.execute(sql_script)
            affect_rows = cursor.rowcount
            conn.commit()
            return affect_rows
        except Exception as ex:
            warning_message = "执行SQL出现异常,请求地址: {}:{}\n请求SQL: {}\n请求参数: {}\n异常信息:{}\n堆栈信息：{}".format(
                self.mysql_host, self.mysql_port,
                sql_script, json.dumps(sql_paras, cls=MySQLJsonEncoder),
                str(ex), traceback.format_exc(),
            )
            self.logger.warning(warning_message)
            raise Exception(str(ex))
        finally:
            if cursor is not None:
                cursor.close()
            if conn is not None:
                conn.close()

    def mysql_exec_many(self, exec_items):
        """
        将多个SQL放到一个事务中执行
        :param exec_items:
        :return:
        """
        cursor = None
        conn = None
        total_affect_rows = 0
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            for sql_script, sql_param in exec_items:
                if sql_param is not None:
                    cursor.execute(sql_script, sql_param)
                else:
                    cursor.execute(sql_script)
                affect_rows = cursor.rowcount
                if affect_rows is not None:
                    total_affect_rows += affect_rows
            conn.commit()
            return total_affect_rows
        except Exception as ex:
            conn.rollback()
            warning_message = "执行出现异常：{}\n堆栈：{}\n参数：{}".format(
                str(ex),
                traceback.format_exc(),
                exec_items
            )
            self.logger.warning(warning_message)
            raise Exception(str(ex))
        finally:
            if cursor is not None:
                cursor.close()
            if conn is not None:
                conn.close()

    def insert_multi_rows(self, database_name, table_name, row_items, with_replace: bool = False):
        if len(row_items) == 0:
            return
        column_names = row_items[0].keys()
        if with_replace:
            sql_header = (
                "REPLACE INTO `{database_name}`.`{table_name}`"
                "({column_names})VALUES "
            )
        else:
            sql_header = (
                "INSERT INTO `{database_name}`.`{table_name}`"
                "({column_names})VALUES "
            )
        sql_header = sql_header.format(
            database_name=database_name,
            table_name=table_name,
            column_names=', '.join(map(lambda key: '`{}`'.format(key), column_names))
        )
        sql_paras = list()
        row_sql_list = []
        for row_item in row_items:
            row_sql = "({})".format(', '.join(map(lambda key: '%s', column_names)))
            row_sql_list.append(row_sql)
            for column_name in column_names:
                sql_paras.append(row_item[column_name])
        sql_script = sql_header + ",".join(row_sql_list)
        return self.mysql_exec(sql_script=sql_script, sql_paras=sql_paras)
